<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd"
        objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">
    <changeSet id="1648541178087-1" author="BJCreslin">
        <createSequence incrementBy="1" sequenceName="hibernate_sequence" startValue="1"/>
    </changeSet>
    <changeSet id="1648541178087-2" author="BJCreslin">
        <createTable tableName="procurements">
            <column name="id" type="serial" autoIncrement="true">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_procurements"/>
            </column>
            <column name="created" type="DATETIME"/>
            <column name="updated" type="DATETIME"/>
            <column defaultValue="ACTIVE" name="status" type="varchar(255)"/>
            <column name="comment" type="CLOB"/>
            <column name="stage" type="VARCHAR(255)"/>
            <column name="uin" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="fz_number" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="application_deadline" type="timestamp with time zone"/>
            <column name="contract_price" type="DECIMAL"/>
            <column name="procedure_type" type="VARCHAR(255)"/>
            <column name="publisher_name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="restrictions" type="VARCHAR(255)"/>
            <column name="link_on_placement" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="application_secure" type="VARCHAR(255)"/>
            <column name="contract_secure" type="VARCHAR(255)"/>
            <column name="object_of" type="CLOB"/>
            <column name="last_updated_from_eis" type="DATE"/>
            <column name="date_time_last_updated" type="DATE"/>
            <column name="date_of_placement" type="DATE"/>
            <column name="date_of_auction" type="timestamp with time zone"/>
        </createTable>
    </changeSet>
    <changeSet id="1648541178087-3" author="BJCreslin">
        <createTable tableName="roles">
            <column name="id" type="serial" autoIncrement="true">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_roles"/>
            </column>
            <column name="created" type="DATETIME"/>
            <column name="updated" type="DATETIME"/>
            <column defaultValue="ACTIVE" name="status" type="varchar(255)"/>
            <column name="comment" type="CLOB"/>
            <column name="rolename" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet id="1648541178087-4" author="BJCreslin">
        <createTable tableName="user_roles">
            <column name="role_id" type="BIGINT" autoIncrement="true">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_user_roles"/>
            </column>
            <column name="user_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_user_roles"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="1648541178087-5" author="BJCreslin">
        <createTable tableName="users">
            <column name="id" type="serial" autoIncrement="true">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_users"/>
            </column>
            <column name="created" type="DATETIME"/>
            <column name="updated" type="DATETIME"/>
            <column defaultValue="ACTIVE" name="status" type="varchar(255)"/>
            <column name="comment" type="CLOB"/>
            <column name="username" type="VARCHAR(255)"/>
            <column name="email" type="VARCHAR(255)"/>
            <column name="password" type="VARCHAR(255)"/>
            <column name="tg_user" type="BIGINT"/>
            <column defaultValue="NO STATEMENT" name="tg_statement" type="varchar(255)"/>
            <column name="enabled" type="BOOLEAN"/>
        </createTable>
    </changeSet>
    <changeSet id="1648541178087-6" author="BJCreslin">
        <createTable tableName="users_procurements">
            <column name="procurement_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_users_procurements"/>
            </column>
            <column name="user_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_users_procurements"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="1648541178087-7" author="BJCreslin">
        <addUniqueConstraint columnNames="uin" constraintName="uc_procurements_uin" tableName="procurements"/>
    </changeSet>
    <changeSet id="1648541178087-8" author="BJCreslin">
        <addUniqueConstraint columnNames="email" constraintName="uc_users_email" tableName="users"/>
    </changeSet>
    <changeSet id="1648541178087-9" author="BJCreslin">
        <addUniqueConstraint columnNames="tg_user" constraintName="uc_users_tg_user" tableName="users"/>
    </changeSet>
    <changeSet id="1648541178087-10" author="BJCreslin">
        <addUniqueConstraint columnNames="username" constraintName="uc_users_username" tableName="users"/>
    </changeSet>
    <changeSet id="1648541178087-11" author="BJCreslin">
        <addForeignKeyConstraint baseColumnNames="procurement_id" baseTableName="users_procurements"
                                 constraintName="fk_usepro_on_procurement" referencedColumnNames="id"
                                 referencedTableName="procurements"/>
    </changeSet>
    <changeSet id="1648541178087-12" author="BJCreslin">
        <addForeignKeyConstraint baseColumnNames="user_id" baseTableName="users_procurements"
                                 constraintName="fk_usepro_on_user" referencedColumnNames="id"
                                 referencedTableName="users"/>
    </changeSet>
    <changeSet id="1648541178087-13" author="BJCreslin">
        <addForeignKeyConstraint baseColumnNames="role_id" baseTableName="user_roles" constraintName="fk_userol_on_role"
                                 referencedColumnNames="id" referencedTableName="roles"/>
    </changeSet>
    <changeSet id="1648541178087-14" author="BJCreslin">
        <addForeignKeyConstraint baseColumnNames="user_id" baseTableName="user_roles" constraintName="fk_userol_on_user"
                                 referencedColumnNames="id" referencedTableName="users"/>
    </changeSet>

</databaseChangeLog>